msDB_to_spss# -*- coding: cp1250 -*-
__author__ =  'Batu Janjgava'
__version__=  '1.0.0'

import os, sys

#sys.path.append(os.path.join(os.path.dirname(__file__), ".."))

import spss, SpssClient, time, spssaux

#import spssbinit
#reload(spssbinit)

#fileOutput_=spssbinit.fileOutput_
#msDB_=spssbinit.msDB_

fileOutput_="./output/results.sav"
msDB_="./output/spss_out.mdb"

print fileOutput_,msDB_



def init():
    # set working directory for SPSS
    SpssClient.StartClient() 
    path = SpssClient.GetDesignatedSyntaxDoc().GetDocumentPath()
    SpssClient.StopClient()
    print path
    path="\\".join(path.split('\\')[:-2])
    print path
    set_working_dir(path)

    ##- set a folder where python codes are stored
    sys.path.append(path+"\code")
    sys.path.append(path+"\code\modules")
    

def set_working_dir(path):

    cmd=r"""CD '%s'."""%(path)
    spss.Submit(cmd)

    
init()

    
#--------------------------------------------------------------
def get_active_datafile_name():
    """Info can be
        Data    name of the active file (empty if unnamed)
        Filter    filter variable or empty
        Weight    weight variable or empty
        SplitFile    comma-separated split variable(s) or empty"""

    return spssaux.getDatasetInfo(info='Data')

       
def out_to_figure_(spssCMD, figName, psize=100, spssDS='*', fileOutput=fileOutput_):
    """Writes figures in /output/figure_files in .jpg  
    """
    
    if spssDS in '*':
        print 'sdsdsds'
        #-- active dataset
        spssDS=spss.ActiveDataset()

    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))
    
    cmd=r"""
        oms  select charts
        /IF SUBTYPES=['Custom Table' 'Ggraph' 'FREQUENCIES']
        /destination
            format = HTML
            IMAGEFORMAT=JPG
            CHARTSIZE=%(psize)s
            IMAGEROOT='%(figName)s'
            outfile = './output/figure.htm'.

        

        %(spssCMD)s

        *DATASET Display.
        
        OMSEND.
        *execute.

        """%locals()


##            IMAGES=YES
##            IMAGEFORMAT=JPG
##            IMAGEROOT='OUTPUT'
##            CHARTFORMAT= IMAGE
##            /if commands = ['%s'] subtypes = ['%s']
    
    spss.Submit(cmd)

    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))

    print "I am here"
    
def out_to_figure(spssCMD, figName, psize=100, spssDS='*', fileOutput=fileOutput_):
    
    if spssDS in '*':
        print 'sdsdsds'
        #-- active dataset
        spssDS=spss.ActiveDataset()

    figType='EMF'
    
    i=figName.find('.')
    if i!=-1:
        figType=figName[i+1:]
        figName=figName[0:i]

    cmd=r"""OUTPUT NEW name= out_to_figure.

        %(spssCMD)s

        OUTPUT EXPORT 
        /%(figType)s IMAGEROOT='./output/figures/%(figName)s' 
        PERCENTSIZE=%(psize)s.

        OUTPUT Close out_to_figure.
    """%locals()
    
    spss.Submit(cmd)

    
    
def out_spss(cmdID,spssDS,spssCMD, fileOutput=fileOutput_):
    """Writes output of spssCMD in .sav   
    """

    if spssDS in '*':
        print 'sdsdsds'
        #-- active dataset
        spssDS=spss.ActiveDataset()
        
    list_cmdID=["crosstab","logreg","desc","reg","roc","npar","freq","freq_stat","corr","ctable"]

                
    i=list_cmdID.index(cmdID)
    
    omsID=[["crosstabs","Crosstabulation"],["Logistic Regression", "Variables in the Equation"],\
           ["Descriptives","Descriptive Statistics"],["Regression","Coefficients"],\
           ["ROC Curve","Area Under the Curve"],["NPar Tests","Two Sample Kolmogorov Smirnov Test Statistics"],\
           ["FREQUENCIES","FREQUENCIES"],["FREQUENCIES","Statistics"],["Correlations","Correlations"],["CTables","Custom Table"]]
    
    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))
    
    cmd=r"""
        oms  select tables
        /if commands = ['%s'] subtypes = ['%s']
        /destination format = sav outfile = '%s'.

        %s

        DATASET Display.
        
        OMSEND.
        *execute.

        
        """%(omsID[i][0],omsID[i][1],fileOutput,spssCMD)
    cmd
    
    spss.Submit(cmd)

    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))
    

def out_spv(cmdID,spssDS,spssCMD, fileOutput):
    """Writes output of spssCMD in .sav   
    """

    list_cmdID=["crosstab","logreg","desc","reg","roc","npar","freq","corr","ctable"]

                
    i=list_cmdID.index(cmdID)
    
    omsID=[["crosstabs","Crosstabulation"],["Logistic Regression", "Variables in the Equation"],\
           ["Descriptives","Descriptive Statistics"],["Regression","Coefficients"],\
           ["ROC Curve","Area Under the Curve"],["NPar Tests","Two Sample Kolmogorov Smirnov Test Statistics"],\
           ["FREQUENCIES","FREQUENCIES"],["Correlations","Correlations"],["CTables","Custom Table"]]
    
    
    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))
    
    cmd=r"""
        oms  select tables
        /if commands = ['%s'] subtypes = ['%s']
        /destination format = spv outfile = '%s'.

        %s

        OMSEND.
        execute.

        
        """%(omsID[i][0],omsID[i][1],fileOutput,spssCMD)
    cmd
    
    spss.Submit(cmd)

    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))

def out_to_word(cmdID,spssDS,spssCMD, fileOutput):
    """Writes output of spssCMD in .sav   
    """

    #spss.Submit("OUTPUT NEW name= spv_to_doc.")
    
    out_spv(cmdID,spssDS,spssCMD, "./figures/tmp.spv")
    #out_spv(cmdID,spssDS,spssCMD, "tmp.spv")

    cmd=r"""
        OUTPUT OPEN FILE='./figures/tmp.spv' NAME=spv_to_doc.
        
        OUTPUT  EXPORT NAME=spv_to_doc
         /CONTENTS EXPORT=VISIBLE
         /DOC DOCUMENTFILE='%s' WIDETABLES= SHRINK NOTESCAPTIONS=NO."""%(fileOutput)

    spss.Submit(cmd)

    spss.Submit("OUTPUT Close spv_to_doc.")


    print cmd
    
    spss.Submit(r"""DATASET ACTIVATE %s."""%(spssDS))


def out_to_word_sql(SQL,spssCMD, fileOutput,msDB=msDB_):
    msDB_to_spss("out_to_word_sql",SQL,msDB)
    out_to_word("ctable","out_to_word_sql",spssCMD, fileOutput)
    spss.Submit("Dataset Close out_to_word_sql.")
    
  
def out_to_msDB(cmdID,spssCMD, tblName=None, spssDS='*',fileOutput=fileOutput_,msDB=msDB_):
    """Writes output of spssCMD in msDB
    """
    print spssCMD

    if spssDS in '*':
        print 'sdsdsds'
        #-- active dataset
        spssDS=spss.ActiveDataset()
        
    out_spss(cmdID,spssDS,spssCMD, fileOutput)

    if tblName==None:
       tblName= cmdID

    print tblName
    
    cmd=r"""
         get file='%s'.
         DATASET NAME %s WINDOW=FRONT.
        """%(fileOutput,tblName)

    spss.Submit(cmd)

    spss_to_msDB(tblName,msDB)

    spss.Submit(r"""
                DATASET CLOSE %s.
                Dataset display.
                DATASET ACTIVATE %s.
                Dataset display.
                """
                %(tblName,spssDS))

def save_to_ms_access(spssDS,msDB, varList="ALL"):
    """saves spass dataset spssDataSet to ms access databaes
       MS daytabase file should already exist
    """

    ##print spssDS, msDB
    
    ##
    """check if varaible names contain '.'.
       if yes change it into '_'
    """
    n=spss.GetVariableCount()
    for i in range(n):
        ss=spss.GetVariableName(i)
        cc=ss.find('.')
        if cc>-1:
            ss_=ss.replace('.','_')
            print ss_
            spss.Submit(r"""
                        RENAME VARIABLES (%s=%s).
                        execute.
                    """%(ss,ss_))
        
    #print varList

    cmd=r"""
        SAVE TRANSLATE  /TYPE = ODBC
        /CONNECT=
        'DSN=MS Access Database;DBQ=%s;'
        'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'
        /TABLE = "%s" /replace
        /KEEP =%s.
        execute."""%(msDB,spssDS,varList)

    ##
    
    #print cmd
    try:
        spss.Submit(cmd)
        print "dasdas"
    except:
        print "adadasdasdasd"
        cmd_0=r"""
        SAVE TRANSLATE  /TYPE = ODBC
        /CONNECT=
        'DSN=MS Access Database;DBQ=%s;'
        'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;'
        /SQL="DROP TABLE %s"
        ."""%(msDB,spssDS)
        spss.Submit(cmd_0)
        print "adadasdasdasd"
        spss.Submit(cmd)

    spss.Submit("execute.")     

def spss_to_msDB(spssDS,msDB=msDB_, varList="ALL"):
    """saves spass dataset spssDataSet to ms access databaes
       MS daytabase file should already exist
    """
    workingdir = spssaux.getShow("DIRECTORY")

    msDB=msDB.replace("./","/")
    msDB=msDB.replace("/","\\")
    msDB=workingdir+msDB
   
    print spssDS, msDB
    save_to_ms_access(spssDS,msDB, varList)


def msDB_to_spss(spssDS,SQL,msDB=msDB_):    
    """reads ms access database msDB in spss with attached SQL query
    """
    print msDB,'\n', spssDS, '\n', SQL, '\n'

    workingdir = spssaux.getShow("DIRECTORY")

    msDB=msDB.replace("./","/")
    msDB=msDB.replace("/","\\")
    msDB=workingdir+msDB
    
    cmd=r"""GET DATA 
          /TYPE=ODBC 
          /CONNECT=
          'DSN=MS Access Database;DBQ=%s;'
          'DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;;QuotedId=Yes' 
          /SQL=%s
          /ASSUMEDSTRWIDTH=255.
          
          DATASET NAME %s WINDOW=FRONT.
          
          """%(msDB,SQL,spssDS)

    print cmd
    
    spss.Submit(cmd)


def msDB_SQL(msDB,tblName,SQL):
    """creates table with SQL statement in msDB (acess database)
    """
    
    msDB_to_spss(tblName,SQL,msDB)
    save_to_ms_access(tblName,msDB)
    
    spss.Submit(r"""DATASET close %s."""%(tblName))

def gini(varList1,varList2,spssDS, tblName=None, partList=None, fileOutput=fileOutput_,msDB=msDB_):
    """writes time series of gini statistics to access msDB
    """

    cmd="""
        dataset activate %s."""%(spssDS)
    
    if partList<>None:
        cmd=cmd+r"""SORT CASES BY %s.
                SPLIT FILE BY %s."""%(partList,partList)
        
    cmd=cmd+r"""
          ROC
          %s BY %s
          /PLOT = CURVE(REFERENCE)
          /PRINT = SE
          /CRITERIA = CUTOFF(INCLUDE) TESTPOS(LARGE) DISTRIBUTION(FREE) CI(95)
          /MISSING = EXCLUDE.
        """%(varList1,varList2)

    if partList<>None:
        cmd=cmd+r""" SPLIT FILE  OFF."""
        
    if tblName<>None:
        tblName=tblName
    else:
        tblName="gini_tseries_"+spssDS
        
    print cmd

    print msDB
    out_to_msDB("roc",cmd, tblName,spssDS, fileOutput,msDB)


def ks(varList1,varList2,spssDS, partList=None, fileOutput=fileOutput_,msDB=msDB_):
    """writes time series of k-s statistics to access msDB
    """

    cmd="""
        dataset activate %s."""%(spssDS)
    
    if partList<>None:
        cmd=cmd+r"""SORT CASES BY %s.
                SPLIT FILE BY %s."""%(partList,partList)
    cmd=r""" 
        SET WORKSPACE 150000.
        NPAR TESTS
        /K-S= %s  BY %s
        /MISSING ANALYSIS."""%(varList1,varList2)
    
    if partList<>None:
        cmd=cmd+r""" SPLIT FILE  OFF."""
  
    out_to_msDB("npar",cmd, "ks_tseries_"+spssDS, spssDS, fileOutput,msDB)

def plot_line(title,varY,varX):
    """ varY should be list, varY=["x"]
    """
    
    ss=""
    for i in varY:
        ss=ss+"valuef("+i+") "
    
    cmd=r"""GRAPH TITLE = '%s'
        /line (MULTIPLE)=%s BY %s.
        """%(title,ss,varX)

    spss.Submit(cmd)

def weight_calc(varList, dsInput, fileOutput=fileOutput_, msDB = msDB_):
           
    ## -- retrive beta coeficents

    SQL="SELECT Var2, B  FROM logistic  WHERE ((Var1='Step 1') AND (B Is Not Null))"

    msDB_to_spss("logcoef",SQL,msDB)

    dataCursor=spss.Cursor()
    dataFile=dataCursor.fetchall()
    dataCursor.close()

    ## -- construct IF  (IntVsLimit_max_cat=1) IntVsLimit_max_score=3.182.

    spss.Submit(r"""dataset activate %s"""%(dsInput))
    spssCMD=""
    k=0
    for i in dataFile:
        if i[0].find('(')<>-1:
            varName=i[0][:i[0].find('(')]
            varCat=i[0][i[0].find('(')+1:i[0].find(')')]
            varBeta=i[1]
            ##print k, varCat
            if k>0 and int(varCat)==1:
                varCat_=str(int(varCat_)+1)
                spssCMD= spssCMD + "IF  (" +varName_+"="+varCat_+") "+varName_+"_score = 0.\n"
                k=0
                ##print varCat
                ##print varName

            spssCMD= spssCMD + "IF  (" +varName+"="+str(varCat)+") "+varName+"_score = "+str(varBeta)+".\n"
            
            varCat_=varCat
            varName_=varName
            k=k+1    

    varCat_=str(k+2)
    spssCMD= spssCMD + "IF  (" +varName_+"="+varCat_+") "+varName_+"_score = 0.\n"

    spss.Submit(spssCMD)

    spss.Submit("execute.")
    
    ## -- calculate std. errors and weights
    varTxt=""
    for i in varList:
        varTxt=varTxt+i+"_score "
        
    spssCMD=r"""
                DESCRIPTIVES  VARIABLES= %s
                /STATISTICS=STDDEV.
                """%(varTxt)


    out_to_msDB("desc",spssCMD, None,dsInput, fileOutput,msDB)
    
def iv_calc(varList, dsInput, varBad, tblname="IVV", fileOutput=fileOutput_, msDB = msDB_):
    ## -- woe and information value
##    varList_=""
##    for i in varList:
##        varList_=varList_+i+" "
    
    spssCMD=r"""
                CROSSTABS 
              /TABLES= %s
              BY %s 
              /FORMAT=AVALUE TABLES 
              /CELLS=COUNT 
              /COUNT ROUND CELL.
              """%(varList,varBad)

    out_to_msDB("crosstab",spssCMD,tblname,dsInput, fileOutput,msDB)

def vif_calc(varList, dsInput, varGood, fileOutput=fileOutput_, msDB = msDB_):
    
    ## ---- VIF calculation
    varList_=""
    for i in varList:
        varList_=varList_+i+" "
        
    spssCMD=r"""REGRESSION 
              /MISSING LISTWISE 
              /STATISTICS R TOL
              /CRITERIA=PIN(.05) POUT(.10) 
              /NOORIGIN 
              /DEPENDENT %s
              /METHOD=ENTER %s.
              """%(varGood,varList_)

    out_to_msDB("reg",spssCMD, "VIF", dsInput, fileOutput,msDB)
    
def get_sql(SQL, msDB= msDB_):

    msDB_to_spss("blib_get_sql",SQL,msDB)

    dataCursor=spss.Cursor()
    dataFile=dataCursor.fetchall()
    dataCursor.close()

    spss.Submit("Dataset Close blib_get_sql.")

    return dataFile


#----------------------------------------------------------------                
def bootstrap(cmdID,spssDS,spssCmd,n, k, viewer='NO'):
    #n - number of bootstrap samples 
    #k - fraction of observations used to construct bootstrap sample,
    #    number : X_{1},...,X_{|k*N|}, where N is the number of observations


    list_cmdID=["crosstab","logreg","desc","reg","roc","npar","freq","corr","ctable"]

                
    i=list_cmdID.index(cmdID)
    
    omsID=[["crosstabs","Crosstabulation"],["Logistic Regression", "Variables in the Equation"],\
           ["Descriptives","Descriptive Statistics"],["Regression","Coefficients"],\
           ["ROC Curve","Area Under the Curve"],["NPar Tests","Two Sample Kolmogorov Smirnov Test Statistics"],\
           ["FREQUENCIES","FREQUENCIES"],["Correlations","Correlations"],["CTables","Custom Table"]]

    ID0=omsID[i][0]
    ID1=omsID[i][1]
    
    cmd=r"""
        PRESERVE.
        SET TVARS NAMES.

        DATASET DECLARE spssblib_bootstrap.

        ***first OMS command just suppresses Viewer output***.
        OMS /DESTINATION VIEWER=%(viewer)s /TAG='suppressall'.

        DATASET ACTIVATE  %(spssDS)s.

        OMS /SELECT TABLES
            /IF COMMANDS=['%(ID0)s'] SUBTYPES=['%(ID1)s']
            /DESTINATION FORMAT=SAV OUTFILE='spssblib_bootstrap'
           /COLUMNS SEQUENCE=[R2 C1]
           /TAG='spssblib_bootsrap'.
        """%locals()
        
    
    cmd=cmd+r"""                             
    COMPUTE dummyvar=1.
    AGGREGATE
      /OUTFILE = * MODE = ADDVARIABLES
      /BREAK=dummyvar
      /filesize=N.
    """

    print cmd
    
    spss.Submit(cmd)

    try:
        for i in range(n):
            cmd=r"""SET SEED RANDOM.
                WEIGHT OFF.
                FILTER OFF.
                DO IF $casenum=1.
                  COMPUTE #samplesize=TRUNC(%(k)s*filesize).
                  COMPUTE #filesize=filesize.
                END IF.
                DO IF (#samplesize>0 and #filesize>0).
                  COMPUTE sampleWeight=rv.binom(#samplesize, 1/#filesize).
                  COMPUTE #samplesize=#samplesize-sampleWeight.
                  COMPUTE #filesize=#filesize-1.
                ELSE.
                  COMPUTE sampleWeight=0.
                END IF.
                WEIGHT BY sampleWeight.
                FILTER BY sampleWeight.
                """%locals()
            
            cmd=cmd+ spssCmd
            print cmd
            spss.Submit(cmd)
    except:
        pass
        

    spss.Submit("DATASET DISPLAY.")
    
    spss.Submit("OMSEND.")


    spss.Submit("DATASET ACTIVATE spssblib_bootstrap.")
    
    cmd=r"""
        FREQUENCIES
          VARIABLES=salbegin_B salbegin_Beta jobtime_B jobtime_Beta
          /FORMAT NOTABLE
          /PERCENTILES= 2.5 97.5
          /HISTOGRAM NORMAL.
          """
    #spss.Submit(cmd)
    
    spss.Submit("RESTORE.")
    
def singl_factor(dsInput,varBad,varList, tblRoc, tblIV=None):
    
    cmd=r"""DATASET ACTIVATE %s.
            FORMATS %s (F1).
            VALUE LABELS %s 1 '1' 0 '0'.
            execute.
            """%(dsInput,varBad,varBad)

    spss.Submit(cmd)

    # ---------------------------- POWER -------
    gini(varList,varBad+" (1)", dsInput, tblRoc)

    if tblIV!=None:
        iv_calc(varList, dsInput, varBad,tblIV)

## ---------------- Multi Factor Analysis -----------------

def multi_factor(dsInput, varDep, varIndep, tblREG, tblW, tblROC):

    cmd=r"""LOGISTIC REGRESSION VARIABLES %(varDep)s
                    /METHOD=ENTER %(varIndep)s
                    /EXTERNAL
                    /SAVE PRED(yHat)
                    /CRITERIA=PIN(.05) POUT(.10) ITERATE(20) CUT(.5)."""%locals()

    print cmd
    out_to_msDB("logreg",cmd, tblREG,dsInput)

    #-------------- Construct Scores for Weights -----------------
    SQL="""\"SELECT %(tblREG)s.Var2 AS Factors, B FROM %(tblREG)s\"+
             \" WHERE (((%(tblREG)s.Var1)='Step 1'))\""""%locals()
    beta1=get_sql(SQL)
    spss.Submit(r"""Dataset activate %(dsInput)s."""%locals())

    S=""
    for i in range(len(beta1)-1):
       var=beta1[i][0]
       beta=beta1[i][1]
       S="""(%s)*%s"""%(beta,var)
       #print S
       cmd="""compute %s_score=%s."""%(var.strip(),S)
       #print cmd
       spss.Submit(cmd)

    spss.Submit("execute.")

    vatListScore1=varIndep.replace(" ","_score ")
    vatListScore1=vatListScore1+"_score"

    cmd=r"""
        DESCRIPTIVES  VARIABLES= %(vatListScore1)s
        /STATISTICS=STDDEV.
        """%locals()

    out_to_msDB("desc",cmd, tblW, dsInput)

    #--------------- POWER--------------

    cmd=r"""ROC
      yHat BY %(varDep)s (1)
      /PLOT = CURVE(REFERENCE)
      /PRINT = SE
      /CRITERIA = CUTOFF(INCLUDE) TESTPOS(LARGE) DISTRIBUTION(FREE) CI(95)
      /MISSING = EXCLUDE.
        delete variable yHat.
      """%locals()

    out_to_msDB("roc",cmd, tblROC, dsInput)
